﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonLibrary.DataModel.Plan;
using System.Data.SqlClient;
using CommonLibrary.DB;
using System.Data;

namespace ServiceApplication.Dao.Plan
{
    public class NotifyDao
    {
        #region basic

        public int AddEntity(NotifyEntity notifyEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@notify_title", notifyEntity.notify_title));
            parameterList.Add(new SqlParameter("@notify_content", notifyEntity.notify_content));
            parameterList.Add(new SqlParameter("@notify_content2", notifyEntity.notify_content2));
            parameterList.Add(new SqlParameter("@creater_id", notifyEntity.creater_id));
            parameterList.Add(new SqlParameter("@notify_createtime", notifyEntity.notify_createtime));
            parameterList.Add(new SqlParameter("@notify_descn", notifyEntity.notify_descn));


            string sql = "insert into plan_notify (notify_title,notify_content,creater_id,notify_createtime,notify_descn,notify_content2) values " +
                " (@notify_title,@notify_content,@creater_id,@notify_createtime,@notify_descn,@notify_content2); " +
                " select @@identity";
            string id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(id);
        }

        public NotifyEntity QueryEntity(int notify_id)
        {
            NotifyEntity notifyEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@notify_id", notify_id));
            string sql = "select notify_id,notify_title,notify_content,creater_id,notify_createtime,notify_descn,notify_content2  from plan_notify where notify_id=@notify_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (null == result) return null;

            notifyEntity = new NotifyEntity();
            notifyEntity.notify_id = int.Parse(result[0][0]);
            notifyEntity.notify_title = result[0][1];
            notifyEntity.notify_content = result[0][2];
            notifyEntity.creater_id = int.Parse(result[0][3]);
            notifyEntity.notify_createtime = result[0][4];
            notifyEntity.notify_descn = result[0][5];
            notifyEntity.notify_content2 = result[0][6];
            return notifyEntity;
        }

        public void DeleteEntity(int notify_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@notify_id", notify_id));
            string sql = "delete from plan_notify where notify_id=@notify_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpadateEntity(NotifyEntity notifyEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@notify_id", notifyEntity.notify_id));
            parameterList.Add(new SqlParameter("@notify_title", notifyEntity.notify_title));
            parameterList.Add(new SqlParameter("@notify_content", notifyEntity.notify_content));
            parameterList.Add(new SqlParameter("@notify_content2", notifyEntity.notify_content2));
            parameterList.Add(new SqlParameter("@creater_id", notifyEntity.creater_id));
            parameterList.Add(new SqlParameter("@notify_createtime", notifyEntity.notify_createtime));
            parameterList.Add(new SqlParameter("@notify_descn", notifyEntity.notify_descn));
            string sql = "update plan_notify set notify_title=@notify_title,notify_content=@notify_content,notify_content2=@notify_content2,creater_id=@creater_id, " +
                             " notify_createtime=@notify_createtime,notify_descn=@notify_descn where notify_id=@notify_id ";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        #endregion

        public DataTable QueryAll()
        {
            string sql = "select notify_id 编码,notify_title 标题,notify_content 内容,notify_content2 内容2,creater_id 创建人编码,u.user_name 创建人,notify_createtime 创建时间,notify_descn 描述 " +
                             " from plan_notify n,jc_user u where u.user_id=n.creater_id order by notify_createtime desc ";

            return DBExecuteUtil.querySqlTable(sql);
        }

        public NotifyEntity QueryLast()
        {
            NotifyEntity notifyEntity = null;
            string sql = " select notify_id,notify_title,notify_content,creater_id,notify_createtime,notify_descn,notify_content2  from plan_notify where " +
                              " notify_createtime=(select max(notify_createtime) from plan_notify) ";

            var result = DBExecuteUtil.querySqlArray(sql);
            if (null == result) return null;

            notifyEntity = new NotifyEntity();
            notifyEntity.notify_id = int.Parse(result[0][0]);
            notifyEntity.notify_title = result[0][1];
            notifyEntity.notify_content = result[0][2];
            notifyEntity.creater_id = int.Parse(result[0][3]);
            notifyEntity.notify_createtime = result[0][4];
            notifyEntity.notify_descn = result[0][5];
            notifyEntity.notify_content2 = result[0][6];
            return notifyEntity;
        }
    }
}
